"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : 导出导入alter分区表语法
Create At   : 2023.05.04
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.创建范围分区表并使用alter语句remove分区
    3.创建list分区表并使用alter语句rebuild单个分区
    4.创建hash分区表并使用alter语句rebuild所有分区
    5.创建范围分区表并使用alter语句reorganize单个分区
    6.创建范围分区表并使用alter语句reorganize所有分区
    7.创建范围分区表并使用alter语句check单个分区
    8.创建范围分区表并使用alter语句check所有分区
    9.创建范围分区表并使用alter语句repair单个分区
    10.创建范围分区表并使用alter语句repair所有分区
    11.创建范围分区表并使用alter语句optimize单个分区
    12.创建范围分区表并使用alter语句optimize所有分区
    13.创建分区表和普通表，并使用alter语句交换分区
    14.创建分区表并使用alter语句add分区
    15.创建分区表并使用alter语句drop分区
    16.创建分区表并使用alter语句truncate分区
    17.创建间隔分区表并使用alter语句truncate所有分区
    18.使用gs_dump工具导出tar格式
    19.gs_restore导入
    20.连接导入库查询表1
    21.连接导入库查询表2和表3
    22.连接导入库查询表4和表5
    23.连接导入库查询表6、表7、表8、表9、表10、表11
    24.连接导入库查询表12、表13的数据
    25.连接导入库查询表14
    26.连接导入库查询表15和表16
    27.连接导入库查询表17和表18
    28.清理环境
Expect      :
    1.成功
    2.成功，remove分区后表变为普通表
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.成功
    11.成功
    12.成功
    13.成功
    14.成功
    15.成功
    16.成功
    17.成功
    18.成功
    19.成功
    20.表1导入成功，表1为普通表
    21.导入分区表成功
    22.重组分区成功
    23.导入成功
    24.交换分区后的数据导入成功
    25.导入分区表成功
    26.表15add分区导入成功，表16drop分区成功
    27.表17p1分区数据清空成功，表18所有分区数据清空成功
    28.成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0056(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_dump_0056"
        self.tb_name = "tb_dump_0056"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0056')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"create database {self.db_name}_01 dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_DATABASE_SUCCESS), '执行失败:' + text)

        text = '--step2: 创建范围分区表并使用alter语句remove分区 ' \
               'expect: 成功，remove分区后表变为普通表--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name};
        create table {self.tb_name}(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));
        alter table {self.tb_name} remove partitioning;
        \d+ {self.tb_name};'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn('ALTER TABLE', sql_result, '执行失败:' + text)
        self.assertNotIn('Partition By RANGE(col1)', sql_result,
                         '执行失败:' + text)

        text = '--step3: 创建list分区表并使用alter语句rebuild单个分区 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_01;
        create table {self.tb_name}_01(col1 int, col2 int)\
        partition by list(col1)\
        (\
        partition p1 values in (1000),\
        partition p2 values in (2000),\
        partition p3 values in (4000));\
        alter table {self.tb_name}_01 rebuild partition p1;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn('ALTER TABLE', sql_result, '执行失败:' + text)

        text = '--step4: 创建hash分区表并使用alter语句rebuild所有分区 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_02;
        create table {self.tb_name}_02(col1 int, col2 int)\
        partition by hash(col1)\
        (partition p1,\
        partition p2,\
        partition p3);
        alter table {self.tb_name}_02 rebuild partition all;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn('ALTER TABLE', sql_result, '执行失败:' + text)

        text = '--step5: 创建范围分区表并使用alter语句reorganize单个分区 ' \
               'expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_03;\
        create table {self.tb_name}_03(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));
        alter table {self.tb_name}_03 reorganize partition p1 into\
        (\
        partition p11 values less than (500),\
        partition p12 values less than (1000));'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step6: 创建范围分区表并使用alter语句reorganize所有分区 ' \
               'expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_04;\
        create table {self.tb_name}_04(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));
        alter table {self.tb_name}_04 reorganize partition p2, p3 \
        into (partition p2 values less than (4000));'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step7: 创建范围分区表并使用alter语句check单个分区 ' \
               'expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_05;\
        create table {self.tb_name}_05(col1 int, col2 int)\
        partition by range(col1)\
        (partition p1 values less than (1000),\
         partition p2 values less than (2000),\
         partition p3 values less than (4000));\
         alter table {self.tb_name}_05 check partition p1;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step8: 创建范围分区表并使用alter语句check所有分区 ' \
               'expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_06;\
         create table {self.tb_name}_06(col1 int, col2 int)\
         partition by range(col1)\
         (partition p1 values less than (1000),\
         partition p2 values less than (2000),\
         partition p3 values less than (4000));\
         alter table {self.tb_name}_06 check partition all;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step9: 创建范围分区表并使用alter语句repair单个分区 ' \
               'expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_07;
        create table {self.tb_name}_07(col1 int, col2 int)\
        partition by range(col1)\
        (partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));\
        alter table {self.tb_name}_07 repair partition p2;;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step10: 创建范围分区表并使用alter语句repair所有分区 ' \
               'expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_08;
        create table {self.tb_name}_08(col1 int, col2 int)\
        partition by range(col1)\
        (partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));\
        alter table {self.tb_name}_08 repair partition all;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step11: 创建范围分区表并使用alter语句optimize单个分区 ' \
               'expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_09;\
        create table {self.tb_name}_09(col1 int, col2 int)\
        partition by range(col1)\
        (partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));\
        alter table {self.tb_name}_09 optimize partition p3;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step12: 创建范围分区表并使用alter语句optimize所有分区 ' \
               'expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_10;\
        create table {self.tb_name}_10(col1 int, col2 int)\
        partition by range(col1)\
        (partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));\
        alter table {self.tb_name}_10 optimize partition all;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step13: 创建分区表和普通表，并使用alter语句交换分区 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_11,{self.tb_name}_12;
        create table {self.tb_name}_11(col1 int, col2 int);\
        insert {self.tb_name}_11 values(20,50);\
        create table {self.tb_name}_12(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));
        insert {self.tb_name}_12 values(900),(1500),(3000);
        alter table {self.tb_name}_12 exchange partition p1 with table \
        {self.tb_name}_11 with validation;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step13: 创建分区表并使用alter语句analyze分区 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_13;
        create table {self.tb_name}_13(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));
        alter table {self.tb_name}_13 analyze partition p1;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn('ALTER TABLE', sql_result, '执行失败:' + text)

        text = '--step14: 创建分区表并使用alter语句add分区 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_14;
        create table {self.tb_name}_14(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));
        alter table {self.tb_name}_14 add partition p4 values less than \
        maxvalue;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step15: 创建分区表并使用alter语句drop分区 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_15;
        create table {self.tb_name}_15(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000),\
        partition p4 values less than maxvalue);
        alter table {self.tb_name}_15 drop partition p4;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step16: 创建分区表并使用alter语句truncate分区 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_16;\
        create table {self.tb_name}_16(col1 int, col2 int)\
        partition by range(col1)\
        (\
        partition p1 values less than (1000),\
        partition p2 values less than (2000),\
        partition p3 values less than (4000));
        insert {self.tb_name}_16 values(900,1),(1500,2),(3000,3);
        alter table {self.tb_name}_16 truncate partition p1;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step17: 创建间隔分区表并使用alter语句truncate所有分区 expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_17;\
        create table {self.tb_name}_17(time_id date, col2 int)\
        partition by range(time_id)\
        interval('1 day')\
        (\
        partition p0 values less than ('2019-02-01 00:00:00'),\
        partition p1 values less than ('2019-02-02 00:00:00'),\
        partition p2 values less than ('2019-02-03 00:00:00'));
        insert {self.tb_name}_17 values('2019-12-01 00:00:00'),\
        ('2019-02-01 08:00:00'),('2019-02-02 08:00:00');
        alter table {self.tb_name}_17 truncate partition all;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '----step18: 使用gs_dump工具导出tar格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port}  " \
                      f"{self.db_name} " \
                      f"-F t " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully", dump_msg,
                      '执行失败:' + text)

        text = '----step19: gs_restore导入 expect: 成功----'
        self.log.info(text)
        gs_restore_cmd = f"source {macro.DB_ENV_PATH};" \
                         f"gs_restore " \
                         f"-p {self.dbuser_node.db_port} " \
                         f"-d {self.db_name}_01 " \
                         f"{self.dump_path}"
        self.log.info(gs_restore_cmd)
        gsql_msg = self.dbuser_node.sh(gs_restore_cmd).result()
        self.log.info(gsql_msg)
        self.assertIn(self.constant.GSQL_RESTORE_SUCCESS_MSG, gsql_msg,
                      '执行失败:' + text)

        text = '----step20: 连接导入库查询表1 expect:表1导入成功，表1为普通表----'
        self.log.info(text)
        sql_cmd = f"select relkind from pg_class where " \
                  f"relname ='{self.tb_name}';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('r', sql_result, '执行失败:' + text)

        text = '----step21: 连接导入库查询表2和表3 expect:导入分区表成功----'
        self.log.info(text)
        sql_cmd = f"select relname, partstrategy from pg_partition where " \
                  f"relname ='{self.tb_name}_01';" \
                  f"select relname, partstrategy from pg_partition where " \
                  f"relname ='{self.tb_name}_02';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('l', sql_result, '执行失败:' + text)
        self.assertIn('h', sql_result, '执行失败:' + text)

        text = '----step22: 连接导入库查询表4和表5 expect:重组分区成功----'
        self.log.info(text)
        sql_cmd = f"select relname from pg_partition where parentid" \
                  f" = (select parentid from pg_partition where " \
                  f"relname = '{self.tb_name}_03') " \
                  f"and parttype = 'p' order by boundaries desc;" \
                  f"select relname from pg_partition where parentid " \
                  f"= (select parentid from pg_partition where relname " \
                  f"= '{self.tb_name}_04') and parttype = 'p' " \
                  f"order by boundaries desc;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('(4 rows)', sql_result, '执行失败:' + text)
        self.assertIn('(2 rows)', sql_result, '执行失败:' + text)

        text = '----step23: 连接导入库查询表6、表7、表8、表9、表10、表11 ' \
               'expect:导入成功----'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name}_05;" \
                  f"show create table {self.tb_name}_06;" \
                  f"show create table {self.tb_name}_07;" \
                  f"show create table {self.tb_name}_08;" \
                  f"show create table {self.tb_name}_09;" \
                  f"show create table {self.tb_name}_10;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertEqual(6, sql_result.count('PARTITION BY RANGE (col1)'),
                         '执行失败:' + text)

        text = '----step24: 连接导入库查询表12、表13的数据 ' \
               'expect:交换分区后的数据导入成功----'
        self.log.info(text)
        sql_cmd = f"select * from {self.tb_name}_11;" \
                  f"select * from {self.tb_name}_12 partition (p1);"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('900 |', sql_result, '执行失败:' + text)
        self.assertIn('20 |   50', sql_result, '执行失败:' + text)

        text = '----step25: 连接导入库查询表14 expect:导入分区表成功----'
        self.log.info(text)
        sql_cmd = f"select relname, partstrategy from pg_partition where " \
                  f"relname ='{self.tb_name}_13';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('r', sql_result, '执行失败:' + text)

        text = '----step26: 连接导入库查询表15和表16 expect:表15add分区导入成功，' \
               '表16drop分区成功----'
        self.log.info(text)
        sql_cmd = f"select relname from pg_partition where parentid" \
                  f" = (select parentid from pg_partition where " \
                  f"relname = '{self.tb_name}_14') " \
                  f"and parttype = 'p' order by boundaries desc;" \
                  f"select relname from pg_partition where parentid " \
                  f"= (select parentid from pg_partition where relname " \
                  f"= '{self.tb_name}_15') and parttype = 'p' " \
                  f"order by boundaries desc;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('(4 rows)', sql_result, '执行失败:' + text)
        self.assertIn('(3 rows)', sql_result, '执行失败:' + text)

        text = '----step27: 连接导入库查询表17和表18 expect:表17p1分区数据清空成功，' \
               '表18所有分区数据清空成功---'
        self.log.info(text)
        sql_cmd = f"select * from {self.tb_name}_16 partition (p1);" \
                  f"select * from {self.tb_name}_17;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count('(0 rows)'), '执行失败:' + text)

    def tearDown(self):
        text = '---step28: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
